package com.opennews.openplatform.service;

import com.opennews.openplatform.common.constant.IncomeExpenseTypeConstant;
import com.opennews.openplatform.common.constant.PaymentTypeConstant;
import com.opennews.openplatform.dto.income.expense.detail.BaseIncomeExpenseDetailDto;
import com.opennews.openplatform.dto.income.expense.detail.CreateIncomeExpenseDetailDto;
import com.opennews.openplatform.dto.income.expense.detail.IncomeExpenseDetailQueryArgument;
import com.opennews.openplatform.dto.income.expense.detail.UpdateIncomeExpenseDetailDto;
import com.opennews.openplatform.entity.tables.records.IncomeExpenseDetailRecord;
import com.opennews.openplatform.jooq.DSLContextWrapper;
import com.opennews.openplatform.myspringbootcore.util.SharedUtil;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.jooq.Condition;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.opennews.openplatform.entity.Tables.*;
import static org.jooq.impl.DSL.and;
import static org.jooq.impl.DSL.countDistinct;

@RequiredArgsConstructor
@Service
public class IncomeExpenseDetailService {
    private final DSLContext dslContext;
    private final DSLContextWrapper dslContextWrapper;
    private final BankService bankService;
    private final IncomeExpenseAnalysisService incomeExpenseAnalysisService;
    private final UserService userService;

    /**
     * Queries all income/expense which associated with provided account group.
     *
     * @param queryArgument: The instance of IncomeExpenseDetailQueryArgument which holds criteria. It contains below properties.
     *                       pageSize: How many records is return in one trip.
     *                       pageIndex: Where to take rows.
     *                       accountGroupId: The user's account group id.
     *                       keyword: The String which matches IncomeExpenseDetail.details and IncomeExpenseDetail.remark properties as SQL LIKE operation. It will be ignored when value is null or empty.
     *                       keywordsSearchCondition: The search condition for multi-keyword. The condition is OR or AND.
     *                       type: The String which matches IncomeExpenseDetail.type property. It will be ignored when value is null or empty.
     *                       detailType: The String which matches IncomeExpenseDetail.detailType property. It will be ignored when value is null or empty.
     *                       paymentType: The String which matches IncomeExpenseDetail.paymentType property. It will be ignored when value is null or empty.
     *                       bankCardId: The String which matches IncomeExpenseDetail.bankCard.id property. It will be ignored when value is null or empty.
     *                       startDate: IncomeExpenseDetail.incomeExpenseDate property must greater than or equal to this Date value. It will be ignored when value is null.
     *                       endDate: IncomeExpenseDetail.incomeExpenseDate property must less than or equal to this Date value. It will be ignored when value is null.
     * @return The list of map instance which contains keys below.
     * id: String of the income expense detail id.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * detailType: String of the detail type which indicates where the income from or the expense goes.
     * paymentType: String of the payment type which how the expense payed.
     * bankId: String of the band id of the bank if the expense is payed by card.
     * bankShortName: String of the short name of the bank if the expense is payed by card.
     * bankLogoUrl: String of the logo url of the bank if the expense is payed by card.
     * bankCardId: String of the id of the bank card if the expense is payed by card.
     * bankCardName: String of the name of the bank card if the expense is payed by card.
     * bankCardIsCreditCard: Bool of if this bank card is credit card.
     * details: String of the income or expense detail.
     */
    public List<Map<String, Object>> queryListByAccountGroup(IncomeExpenseDetailQueryArgument queryArgument) {
        // The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        // If the start date and end date are both 2019-12-13 00:00:00, and we want to search the data between start and end date
        // we have to change the end date to 2019-12-14 00:00:00.
        // That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        //if (queryArgument.endDate != null) {
        //    use(TimeCategory) {
        //        queryArgument.endDate = queryArgument.endDate + 1.day
        //    }
        //}

        Condition sharedCondition = getQueryByAccountGroupSharedCondition(queryArgument);

        return dslContext.select(
                        INCOME_EXPENSE_DETAIL.ID.as("id"),
                        INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                        INCOME_EXPENSE_DETAIL.AMOUNT.as("amount"),
                        INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                        INCOME_EXPENSE_DETAIL.DETAIL_TYPE.as("detailType"),
                        INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                        BANK.ID.as("bankId"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK_CARD.ID.as("bankCardId"),
                        BANK_CARD.NAME.as("bankCardName"),
                        BANK_CARD.IS_CREDIT_CARD.as("bankCardIsCreditCard"),
                        INCOME_EXPENSE_DETAIL.DETAILS.as("details")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
                .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(sharedCondition)
                .orderBy(
                        INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.desc(),
                        INCOME_EXPENSE_DETAIL.TYPE.desc(),
                        INCOME_EXPENSE_DETAIL.BANK_CARD_ID.asc(),
                        INCOME_EXPENSE_DETAIL.LAST_UPDATED.desc()
                )
                .limit(queryArgument.pageSize)
                .offset(queryArgument.pageIndex * queryArgument.pageSize)
                .fetch()
                .map(p -> {
                    Map<String, Object> item = p.intoMap();
                    item.put("bankLogoUrl", bankService.getLogoUrl((String) item.get("bankShortName")));

                    return item;
                });
    }

    /**
     * Queries all income/expense total amount which associated with provided account group.
     *
     * @param queryArgument: The instance of IncomeExpenseDetailQueryArgument which holds criteria. It contains below properties.
     *                       pageSize: How many records is return in one trip.
     *                       pageIndex: Where to take rows.
     *                       accountGroupId: The user's account group id.
     *                       keyword: The String which matches IncomeExpenseDetail.details and IncomeExpenseDetail.remark properties as SQL LIKE operation. It will be ignored when value is null or empty.
     *                       keywordsSearchCondition: The search condition for multi-keyword. The condition is OR or AND.
     *                       type: The String which matches IncomeExpenseDetail.type property. It will be ignored when value is null or empty.
     *                       detailType: The String which matches IncomeExpenseDetail.detailType property. It will be ignored when value is null or empty.
     *                       paymentType: The String which matches IncomeExpenseDetail.paymentType property. It will be ignored when value is null or empty.
     *                       bankCardId: The String which matches IncomeExpenseDetail.bankCard.id property. It will be ignored when value is null or empty.
     *                       startDate: IncomeExpenseDetail.incomeExpenseDate property must greater than or equal to this Date value. It will be ignored when value is null.
     *                       endDate: IncomeExpenseDetail.incomeExpenseDate property must less than or equal to this Date value. It will be ignored when value is null.
     * @return The number of the total amount.
     */
    public BigDecimal queryTotalAmountByAccountGroup(IncomeExpenseDetailQueryArgument queryArgument) {
        //// The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        //// If the start date and end date are both 2019-12-13 00:00:00 and we want to search the data between start and end date
        //// we have to change the end date to 2019-12-14 00:00:00.
        //// That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        //if (queryArgument.endDate != null) {
        //    use(TimeCategory) {
        //        queryArgument.endDate = queryArgument.endDate + 1.day
        //    }
        //}

        Condition shareCondition = getQueryByAccountGroupSharedCondition(queryArgument);

        BigDecimal totalAmount = dslContext.select(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT))
                .from(INCOME_EXPENSE_DETAIL)
                .where(shareCondition)
                .fetchOne(0, BigDecimal.class);

        return totalAmount != null ? totalAmount : BigDecimal.ZERO;
    }

    /**
     * Queries which bank card id is used by income/expense based on provided bank card id list.
     *
     * @param bankCardIds:    The string list of bank card id.
     * @param accountGroupId: The string of user accout group id.
     * @return The string list of bank card id which is used by income/expense. Any unused bank card id will be filtered.
     */
    public List<String> queryUsedBankCardIds(List<String> bankCardIds, String accountGroupId) {
        if (!bankCardIds.isEmpty()) {
            return dslContext.selectDistinct(INCOME_EXPENSE_DETAIL.BANK_CARD_ID)
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.BANK_CARD_ID.in(bankCardIds)
                                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    )
                    .fetchInto(String.class);
        } else {
            return new ArrayList<>();
        }
    }

    /**
     * Queries the income/expense list and day summary for account group by id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @return The map instance which contains keys below.
     * list: The List<Map<String, ?>> return by queryListByAccountGroup. Which contains
     * an extra item named daySummary which is the List<Map<String, ?>> return by queryDailySummaryList.
     */
    public List<Map<String, Object>> queryListWithSummaryByAccountGroup(String accountGroupId, Integer pageSize, Integer pageIndex) {
        // Prepares the query argument.
        IncomeExpenseDetailQueryArgument queryArgument = new IncomeExpenseDetailQueryArgument();
        queryArgument.accountGroupId = accountGroupId;
        queryArgument.pageSize = pageSize;
        queryArgument.pageIndex = pageIndex;

        // Queries the income/expense list by argument.
        List<Map<String, Object>> list = queryListByAccountGroup(queryArgument);

        // Gets date list from the detail list.
        List<LocalDateTime> dates = list.stream().map(p -> (LocalDateTime) p.get("incomeExpenseDate")).toList();

        // Queries month summary based on the unique date list.
        List<Map<String, Object>> monthSummaryList = incomeExpenseAnalysisService.queryMonthlySummaryList(accountGroupId, dates);

        // Goes through the month summary list.
        for (Map<String, Object> summary : monthSummaryList) {
            // Clones the item in order to convert its date format.
            // The original item date property used to match the detail data.
            Map<String, Object> summaryWithFormattedDate = new HashMap<>(summary);

            // Adds monthSummary to the first item of each month.
            list.stream().filter(p -> YearMonth.from((LocalDateTime) p.get("incomeExpenseDate")).equals(YearMonth.from((LocalDateTime) summary.get("incomeExpenseDate"))))
                    .findFirst()
                    .get()
                    .put("monthSummary", summaryWithFormattedDate);
        }

        // Queries day summary based on the unique date list.
        List<Map<String, Object>> daySummaryList = incomeExpenseAnalysisService.queryDailySummaryList(accountGroupId, dates);

        // Goes through the day summary list.
        for (Map<String, Object> summary : daySummaryList) {
            // Clones the item in order to convert its date format.
            // The original item date property used to match the detail data.
            Map<String, Object> summaryWithFormattedDate = new HashMap<>(summary);

            // Adds daySummary to the first item of each date.
            list.stream().filter(p -> p.get("incomeExpenseDate").equals(summary.get("incomeExpenseDate")))
                    .findFirst()
                    .get()
                    .put("daySummary", summaryWithFormattedDate);
        }

        // Returns result
        return list;
    }

    /**
     * Queries income expense details by its id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param id:             The string of the income expense detail id.
     * @return The map instance which contains keys below.
     * id: String of the income expense detail id.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * detailType: String of the detail type which indicates where the income from or the expense goes.
     * paymentType: String of the payment type which how the expense pay.
     * bankId: String of the band id of the bank card if the expense is pay by card.
     * bankShortName: String of the short name of the bank card if the expense is pay by card.
     * bankCardId: String of the id of the bank card if the expense is pay by card.
     * bankCardName: String of the name of the bank card if the expense is pay by card.
     * bankCardEnabled: Bool to indicate if this card is enable.
     * details: String of the income or expense detail.
     * remark: String of the necessary extra remark.
     * latitude: Float of location latitude.
     * longitude: Float of location longitude.
     * address: String of location address.
     * formattedAddress: String of location formattedAddress.
     * province: String of location province.
     * city: String of location city.
     * district: String of location district.
     * createdBy: String of username of who created this item.
     * updatedBy: String of username of who updated this item.
     * dateCreated: Date of when this detail created.
     * lastUpdated: Date of when this detail updated.
     */
    public Map<String, Object> queryDetails(String accountGroupId, String id) {
        // Queries detail by account group id.
        Map<String, Object> result = dslContext.select(
                        INCOME_EXPENSE_DETAIL.ID.as("id"),
                        INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                        INCOME_EXPENSE_DETAIL.AMOUNT.as("amount"),
                        INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                        INCOME_EXPENSE_DETAIL.DETAIL_TYPE.as("detailType"),
                        INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                        BANK.ID.as("bankId"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK_CARD.ID.as("bankCardId"),
                        BANK_CARD.NAME.as("bankCardName"),
                        BANK_CARD.IS_CREDIT_CARD.as("bankCardIsCreditCard"),
                        INCOME_EXPENSE_DETAIL.DETAILS.as("details"),
                        INCOME_EXPENSE_DETAIL.REMARK.as("remark"),
                        INCOME_EXPENSE_DETAIL.LATITUDE.as("latitude"),
                        INCOME_EXPENSE_DETAIL.LONGITUDE.as("longitude"),
                        INCOME_EXPENSE_DETAIL.ADDRESS.as("address"),
                        INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.as("formattedAddress"),
                        INCOME_EXPENSE_DETAIL.PROVINCE.as("province"),
                        INCOME_EXPENSE_DETAIL.CITY.as("city"),
                        INCOME_EXPENSE_DETAIL.DISTRICT.as("district"),
                        INCOME_EXPENSE_DETAIL.CREATED_BY.as("createdBy"),
                        INCOME_EXPENSE_DETAIL.UPDATED_BY.as("updatedBy"),
                        INCOME_EXPENSE_DETAIL.DATE_CREATED.as("dateCreated"),
                        INCOME_EXPENSE_DETAIL.LAST_UPDATED.as("lastUpdated")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
                .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(
                        INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL.ID.eq(id))
                )
                .fetchOneMap();

        if (result == null) {
            result = new HashMap<>();
        } else {
            // Queries createdBy & updatedBy basic info.
            String createdBy = result.get("createdBy").toString();
            String updatedBy = result.get("updatedBy").toString();
            List<Map<String, Object>> userDetails = userService.queryBasicInfo(accountGroupId, List.of(createdBy, updatedBy));
            result.put("createdBy", userDetails.stream().filter(p -> p.get("username").equals(createdBy)).findFirst().get());
            result.put("updatedBy", userDetails.stream().filter(p -> p.get("username").equals(updatedBy)).findFirst().get());

            // Sets the bank logo url based on its short name.
            result.put("bankLogoUrl", bankService.getLogoUrl((String) result.get("bankShortName")));
        }

        return result;
    }

    /**
     * Queries the specific credit card bill based on bank card id, bill year and bill month.
     * <p>
     * We don't generate the bill at this moment by using CreditCardBill and CreditCardBillExpenseDetail because that
     * would introduce more complex logic and effort.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @param bankCardId:     The String of bank card id.
     * @param statementDate:  The statement date of bill.
     * @return The return result of queryListByAccountGroup method.
     */
    public List<Map<String, Object>> queryCreditCardBill(String accountGroupId, Integer pageSize, Integer pageIndex, String bankCardId, LocalDateTime statementDate) {
        // Queries the statement date of the credit card with its id, statement year and month.
        Map<String, LocalDateTime> cardStatementDateRange = createCreditCardBillStatementDateRange(statementDate);

        // Prepares the query argument.
        IncomeExpenseDetailQueryArgument queryArgument = new IncomeExpenseDetailQueryArgument();
        queryArgument.accountGroupId = accountGroupId;
        queryArgument.bankCardId = bankCardId;
        queryArgument.pageSize = pageSize;
        queryArgument.pageIndex = pageIndex;
        queryArgument.startDate = cardStatementDateRange.get("statementStartDate");
        queryArgument.endDate = cardStatementDateRange.get("statementEndDate");

        // Queries the list which matches the date and bank card.
        return queryListByAccountGroup(queryArgument);
    }

    /**
     * Queries the credit card bill total amount.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param bankCardId:     The string of bank card id.
     * @param statementDate:  The statement date of bill.
     * @return The BigDecimal of the total amount.
     */
    public BigDecimal queryCreditCardBillAmount(String accountGroupId, String bankCardId, LocalDateTime statementDate) {
        // Queries the statement date of the credit card with its id, statement year and month.
        Map<String, LocalDateTime> cardStatementDateRange = createCreditCardBillStatementDateRange(statementDate);

        // Prepares the query argument.
        IncomeExpenseDetailQueryArgument queryArgument = new IncomeExpenseDetailQueryArgument();
        queryArgument.accountGroupId = accountGroupId;
        queryArgument.bankCardId = bankCardId;
        queryArgument.startDate = cardStatementDateRange.get("statementStartDate");
        queryArgument.endDate = cardStatementDateRange.get("statementEndDate");

        // Queries the total amount within the card statement date range.
        return queryTotalAmountByAccountGroup(queryArgument);
    }

    /**
     * Queries basic statistics including totalIncomeExpenseCount and totalIncomeExpenseDays.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The map instance which contains keys below.
     * totalIncomeExpenseCount: Int of total income/expense recorded.
     * totalIncomeExpenseDays: Int of total income/expense days recorded.
     * totalUserCount: Int of total account group users.
     */
    public Map<String, Object> queryBasicStatistics(String accountGroupId) {
        int totalUserCount = dslContextWrapper.count(ACCOUNT_GROUP_USER, ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId));
        int totalIncomeExpenseCount = dslContextWrapper.count(INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId));
        int totalBankCardCount = dslContextWrapper.count(BANK_CARD, BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId));

        Integer totalIncomeExpenseDays = dslContext.select(countDistinct(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
                .from(INCOME_EXPENSE_DETAIL)
                .where(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                .fetchOne(0, Integer.class);

        return Map.of(
                "totalUserCount", totalUserCount,
                "totalBankCardCount", totalBankCardCount,
                "totalIncomeExpenseCount", totalIncomeExpenseCount,
                "totalIncomeExpenseDays", totalIncomeExpenseDays != null ? totalIncomeExpenseDays : 0
        );
    }

    public List<Map<String, Object>> queryCoordinates(String accountGroupId, String year) {
        // Locations contain same formatted address but different coordinates which we like to take only one match.
        // https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
        var subquery = dslContext.select(DSL.max(INCOME_EXPENSE_DETAIL.ID))
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                        INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(IncomeExpenseTypeConstant.EXPENSE))
                                .and(INCOME_EXPENSE_DETAIL.LATITUDE.isNotNull())
                                .and(INCOME_EXPENSE_DETAIL.LONGITUDE.isNotNull())
                                .and(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.isNotNull())
                                .and(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.ne(""))
                                .and(
                                        SharedUtil.isNullOrEmpty(year) ?
                                                DSL.trueCondition() :
                                                DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(Integer.valueOf(year))
                                )
                )
                .groupBy(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS);

        return dslContext.select(
                        INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.as("formattedAddress"),
                        INCOME_EXPENSE_DETAIL.LATITUDE.as("latitude"),
                        INCOME_EXPENSE_DETAIL.LONGITUDE.as("longitude")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                        INCOME_EXPENSE_DETAIL.ID.in(subquery)
                )
                .fetchMaps();
    }

    /**
     * Adds a new record of IncomeExpenseDetail with posted data.
     * The username used to query the account group id and saved to createdBy and updatedBy.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which is saved to createdBy and updatedBy.
     * @param data:           The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     */
    public void add(String accountGroupId, String username, @Valid CreateIncomeExpenseDetailDto data) {
        // Creates IncomeExpenseDetail based on dto.
        IncomeExpenseDetailRecord record = createRecord(data, username);

        // Associates with account group.
        record.setAccountGroupId(accountGroupId);

        // Specifies creator.
        record.setCreatedBy(username);

        // Saves change.
        record.insert();
    }

    /**
     * Updates existing IncomeExpenseDetail with posted data.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which saved to updatedBy.
     * @param data:           The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     */
    public void update(String accountGroupId, String username, @Valid UpdateIncomeExpenseDetailDto data) {
        // Just makes sure only updates owned data.
        if (dslContextWrapper.count(INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ID.eq(data.getId())
                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))) == 1) {
            IncomeExpenseDetailRecord record = createRecord(data, username);
            record.changed(INCOME_EXPENSE_DETAIL.ID, false);

            // Specifies updater.
            record.setUpdatedBy(username);

            record.update();
        }
    }

    /**
     * Deletes the IncomeExpenseDetails with provides list of id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param ids             The string list of id.
     */
    public void delete(String accountGroupId, List<String> ids) {
        if (!ids.isEmpty()) {
            // Executes batch deleting by DetachedCriteria.
            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.ID.in(ids)
                                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    )
                    .execute();
        }
    }

    /**
     * Creates IncomeExpenseDetailRecord based on BaseIncomeExpenseDetailDto.
     *
     * @param data:     The BaseIncomeExpenseDetailDto instance holds data.
     * @param username: The string of username which saved to updatedBy.
     */
    private IncomeExpenseDetailRecord createRecord(@Valid BaseIncomeExpenseDetailDto data, String username) {
        IncomeExpenseDetailRecord record = dslContext.newRecord(INCOME_EXPENSE_DETAIL);

        // Updates the bank card property.
        record.setBankCardId(data.getBankCard() != null ? data.getBankCard().getId() : null);

        // Updates all properties which data contains.
        record.from(data);

        // Specifies updater.
        record.setUpdatedBy(username);

        // Sets the default payment type if needed.
        if (SharedUtil.isNullOrEmpty(record.getPaymentType())) {
            record.setPaymentType(PaymentTypeConstant.CASH);
        }

        // Just in case user inputs negative number for income and positive number for expense.
        // We take the absolute value first.
        record.setAmount(record.getAmount().abs());

        // If it is expense we take it as negative number.
        if (record.getType().equals(IncomeExpenseTypeConstant.EXPENSE)) {
            record.setAmount(record.getAmount().negate());
        }

        return record;
    }

    /**
     * Creates the shared delegate closure for criteria usage. Especially for queryListByAccountGroup and queryTotalAmountByAccountGroup methods.
     *
     * @param queryArgument: The instance of IncomeExpenseDetailQueryArgument which holds criteria. It contains below properties.
     *                       pageSize: How many records is return in one trip.
     *                       pageIndex: Where to take rows.
     *                       accountGroupId: The user's account group id.
     *                       keyword: The String which matches IncomeExpenseDetail.details, IncomeExpenseDetail.remark and IncomeExpenseDetail.formattedAddress properties as SQL LIKE operation. It will be ignored when value is null or empty.
     *                       keywordsSearchCondition: The search condition for multi-keyword. The condition is OR or AND.
     *                       type: The String which matches IncomeExpenseDetail.type property. It will be ignored when value is null or empty.
     *                       detailType: The String which matches IncomeExpenseDetail.detailType property. It will be ignored when value is null or empty.
     *                       paymentType: The String which matches IncomeExpenseDetail.paymentType property. It will be ignored when value is null or empty.
     *                       bankCardId: The String which matches IncomeExpenseDetail.bankCard.id property. It will be ignored when value is null or empty.
     *                       startDate: IncomeExpenseDetail.incomeExpenseDate property must greater than or equal to this Date value. It will be ignored when value is null.
     *                       endDate: IncomeExpenseDetail.incomeExpenseDate property must less than or equal to this Date value. It will be ignored when value is null.
     * @return
     */
    private Condition getQueryByAccountGroupSharedCondition(IncomeExpenseDetailQueryArgument queryArgument) {
        // The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        // If the start date and end date are both 2019-12-13 00:00:00 and we want to search the data between start and end date
        // we have to change the end date to 2019-12-14 00:00:00.
        // That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        if (queryArgument.endDate != null) {
            queryArgument.endDate = queryArgument.endDate.plusDays(1);
        }

        Condition condition = and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(queryArgument.accountGroupId));

        if (queryArgument.startDate != null) {
            // Where a property is greater than or equal to a particular value.
            condition = condition.and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.ge(queryArgument.startDate));
        }

        if (queryArgument.endDate != null) {
            // Where a property is less than a particular value.
            condition = condition.and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.lt(queryArgument.endDate));
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
            String[] keywords = queryArgument.keyword.split("\\s+");
            Condition keywordCondition = DSL.noCondition();

            // Determines the search logic (AND vs OR) based on user input.
            boolean isAndCondition = queryArgument.keywordsSearchCondition.equals(IncomeExpenseDetailQueryArgument.KEYWORDS_SEARCH_AND_CONDITION);

            for (String keyword : keywords) {
                // For each keyword, create the condition for matching in `details`, `remark`, and `formatted_address`
                Condition keywordMatch = INCOME_EXPENSE_DETAIL.DETAILS.like("%" + keyword + "%")
                        .or(INCOME_EXPENSE_DETAIL.REMARK.like("%" + keyword + "%"))
                        .or(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.like("%" + keyword + "%"));

                if (isAndCondition) {
                    // If it's an AND condition, we need to ensure that all fields match the keyword.
                    keywordCondition = keywordCondition.and(keywordMatch);
                } else {
                    // If it's an OR condition, we add the keyword condition to the OR group.
                    keywordCondition = keywordCondition.or(keywordMatch);
                }
            }

            condition = condition.and(keywordCondition);
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.type)) {
            condition = condition.and(INCOME_EXPENSE_DETAIL.TYPE.eq(queryArgument.type));
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.detailType)) {
            condition = condition.and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(queryArgument.detailType));
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.paymentType)) {
            condition = condition.and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(queryArgument.paymentType));
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.bankCardId)) {
            condition = condition.and(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(queryArgument.bankCardId));
        }

        return condition;
    }

    /**
     * Creates the credit card statement date range.
     *
     * @param statementDate: The Date of this month statement date.
     * @return The map instance which contains keys below.
     * statementStartDate: The start date of the statement date.
     * statementEndDate: The end date of the statement date.
     */
    private Map<String, LocalDateTime> createCreditCardBillStatementDateRange(LocalDateTime statementDate) {
        return Map.of(
                "statementStartDate", statementDate.minusMonths(1),
                "statementEndDate", statementDate.minusDays(1)
        );
    }
}

